

ALTER PROCEDURE SP_SAVE_BOM_INVENTORY
@LOT_ID INT, 
@WAFER_ID INT, 
@BIN1_QTY INT,
@BIN1_BALANCE_QTY INT, 
@IS_SCRAP BIT --0 means the wafer is scrapped
 
AS 

BEGIN
	DECLARE @BIN1_SCRAP_QTY INT
	--inserting into sort_bin_release_table to make available for assm release
	INSERT INTO SORT_BIN_RELEASE_TABLE( LOT_ID,WAFER_ID,BIN_1,Bin_1_Balance_Qty ) VALUES ( @LOT_ID, @WAFER_ID, @BIN1_QTY, @BIN1_BALANCE_QTY )
	
	--To Move the Scrapped wafer in to engg lot table.
	IF( @IS_SCRAP = 0 )
	BEGIN
		--Calculating the Scrap Quantity
		SET @BIN1_SCRAP_QTY = (SELECT BIN_1 - (SELECT ISNULL(SUM(QTY_PULLED),0)  FROM BOM_RELEASE_LOT_TABLE BRLT, Assm_Release_table ASM WHERE ASM.ASSEMBLY_RELEASE_ID = BRLT.ASSEMBLY_RELEASE_ID AND LOT_ID = @LOT_ID and WAFER_ID = @WAFER_ID AND ASM.STATUS <> 'Cancelled') FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID)
		IF( (SELECT COUNT(*) FROM ENGINEERING_LOTS WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID) = 0 AND @BIN1_SCRAP_QTY > 0 )
		BEGIN
			INSERT INTO ENGINEERING_LOTS( LOT_ID, WAFER_ID, BIN_1, MOVED_TO, TIMESTAMP) VALUES (@LOT_ID, @WAFER_ID, @BIN1_SCRAP_QTY, 'Scrap', CURRENT_TIMESTAMP)   
		END
	END
END